# 修复导入问题，添加缺失的Flask组件导入
from flask import Flask, render_template, request, session, redirect, url_for, jsonify, send_file, send_from_directory, abort, g
import sqlite3
import os
import json
import uuid
from datetime import datetime
from db_manager import DatabaseManager
from pathlib import Path

app = Flask(__name__)
app.secret_key = 'your-secret-key'  # 用于session管理的密钥

# 数据库配置
DATABASE = 'yetian.db'
# 配置基础图片目录
BASE_IMAGE_DIR = "/www/python_project/pdf_of/spider3/upload/news_img"
# 初始化数据库管理器
def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = DatabaseManager(DATABASE)
    return db

def get_image_path(date_str, filename):
    """根据日期和文件名获取图片完整路径"""
    image_path = Path(BASE_IMAGE_DIR) / date_str / filename
    return image_path
    
# 首页路由
@app.route('/')
@app.route('/index')
def index():
    # 查询热门新闻（前5条）
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute("SELECT id, title, face_img,publish_at FROM news WHERE is_del=0 ORDER BY publish_at DESC LIMIT 5")
    hot_news = cursor.fetchall()
    conn.close()

    # 格式化响应数据
    formatted_hot_news = []
    for news in hot_news:
        face_img = news[2] if news[2] else '/static/images/default.jpg'
        if face_img == '/':
            face_img = '/static/images/default.jpg'
        formatted_hot_news.append([
            news[0],
            news[1],
            face_img,
            news[3]
        ])
    
    return render_template('index.html', hot_news=formatted_hot_news)

# 搜索功能API
@app.route('/news/search', methods=['GET'])
def search_news():
    keyword = request.args.get('keyword', '', type=str).strip()
    page = request.args.get('page', 1, type=int)
    per_page = 10
    offset = (page - 1) * per_page
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 如果没有关键字，返回空结果
    if not keyword:
        conn.close()
        # return jsonify({
        #     'news': [],
        #     'has_more': False,
        #     'page': page,
        #     'keyword': keyword
        # })

        # 渲染搜索结果页面
        return render_template('search_result.html', 
                               news=[], 
                               keyword='', 
                               has_more=False, 
                               page=1)
    
    # 构建搜索查询
    query = "SELECT id, title, face_img, publish_at FROM news WHERE is_del=0 AND title LIKE ? ORDER BY publish_at DESC LIMIT ? OFFSET ?"
    count_query = "SELECT COUNT(*) FROM news WHERE is_del=0 AND title LIKE ?"
    params = [f"%{keyword}%", per_page, offset]
    count_params = [f"%{keyword}%"]
    
    cursor.execute(query, params)
    news_list = cursor.fetchall()
    
    # 检查是否还有更多数据
    cursor.execute(count_query, count_params)
    total = cursor.fetchone()[0]
    has_more = (offset + per_page) < total
    
    conn.close()
    
    # 格式化响应数据
    formatted_news = []
    for news in news_list:
        face_img = news[2] if news[2] else '/static/images/default.jpg'
        if face_img == '/':
            face_img = '/static/images/default.jpg'
        formatted_news.append({
            'id': news[0],
            'title': news[1],
            'face_img': face_img,
            'publish_at': news[3]
        })
    
    # 渲染搜索结果页面或返回JSON数据
    if request.args.get('format') == 'json':
        return jsonify({
            'news': formatted_news,
            'has_more': has_more,
            'page': page,
            'keyword': keyword
        })
    else:
        # 渲染搜索结果页面
        return render_template('search_result.html', 
                               news=formatted_news, 
                               keyword=keyword, 
                               has_more=has_more, 
                               page=page)

# 新闻列表API（用于前端瀑布流加载）
@app.route('/api/news', methods=['GET'])
def get_news():
    page = request.args.get('page', 1, type=int)
    per_page = 10
    offset = (page - 1) * per_page
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute("SELECT id, title, face_img, publish_at FROM news WHERE is_del=0 ORDER BY publish_at DESC LIMIT ? OFFSET ?", (per_page, offset))
    news_list = cursor.fetchall()
    
    # 检查是否还有更多数据
    cursor.execute("SELECT COUNT(*) FROM news WHERE is_del=0")
    total = cursor.fetchone()[0]
    has_more = (offset + per_page) < total
    
    conn.close()

    # 格式化响应数据
    formatted_news = []
    for news in news_list:
        face_img = news[2] if news[2] else '/static/images/default.jpg'
        if face_img == '/':
            face_img = '/static/images/default.jpg'
        formatted_news.append({
            'id': news[0],
            'title': news[1],
            'face_img': face_img,
            'publish_at': news[3]
        })
    
    return jsonify({
        'news': formatted_news,
        'has_more': has_more,
        'page': page
    })

# 新闻详情页
@app.route('/news/<int:news_id>')
def news_detail(news_id):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    cursor.execute("SELECT title, publish_at, url, order_num FROM news WHERE id=? AND is_del=0", (news_id,))
    news = cursor.fetchone()
    
    # 查询热门新闻
    cursor.execute("SELECT id, title, face_img FROM news WHERE is_del=0 AND id!=? ORDER BY publish_at DESC LIMIT 5", (news_id,))
    hot_news = cursor.fetchall()
    
    conn.close()
    
    if not news:
        return '新闻不存在或已删除', 404
    txt_path = app.root_path+"/upload/news_txt/"
    # 读取新闻内容文件
    content = ""
    try:
        # 构建文件路径
        publish_date = news[1]  # 发布时间
        order_num = news[3]  # 新闻编号
        # 将发布时间转换为年月日格式 (这里简化处理，直接使用日期部分)
        datestr = publish_date.split(' ')[0]  # 假设格式为 "2023-10-01 12:30:45"
        datestr = datestr.replace('-', '')
        file_path = txt_path+f"{datestr}/{order_num}.txt"
        
        # 检查文件是否存在
        if os.path.exists(file_path):
            with open(file_path, 'r', encoding='utf-8') as f:
                file_content = f.read()
                
                # 提取被{{{}}}括起来的内容
                start_marker = '{{{'
                end_marker = '}}}'
                start_pos = file_content.find(start_marker)
                end_pos = file_content.find(end_marker)
                
                if start_pos != -1 and end_pos != -1:
                    content = file_content[start_pos + len(start_marker):end_pos]
        
    except Exception as e:
        print(f"读取新闻内容文件时出错: {e}")
    
    return render_template('news_detail.html', news={'id': news_id, 'title': news[0], 'publish_at': news[1], 'url': news[2], 'content': content}, hot_news=hot_news)

# 后台入口页面
@app.route('/admin')
def admin_index():
    # 简单的登录验证
    if 'admin_logged_in' not in session:
        return redirect(url_for('admin_login'))
    
    # 获取搜索参数
    keyword = request.args.get('keyword', '')
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    app_id = request.args.get('app_id', '')
    
    # 获取页码参数，默认第1页
    try:
        page = int(request.args.get('page', 1))
        if page < 1:
            page = 1
    except ValueError:
        page = 1
    
    # 每页显示的条数
    per_page = 10
    # 计算偏移量
    offset = (page - 1) * per_page
    
    # 构建查询条件
    query = "SELECT id, title, face_img, order_num, publish_at, media_code, is_del FROM news WHERE 1"
    count_query = "SELECT COUNT(*) FROM news WHERE 1"
    params = []
    count_params = []
    
    if keyword:
        query += " AND title LIKE ?"
        count_query += " AND title LIKE ?"
        params.append(f"%{keyword}%")
        count_params.append(f"%{keyword}%")
    if start_date:
        query += " AND publish_at >= ?"
        count_query += " AND publish_at >= ?"
        params.append(start_date)
        count_params.append(start_date)
    if end_date:
        query += " AND publish_at <= ?"
        count_query += " AND publish_at <= ?"
        params.append(end_date + " 23:59:59")
        count_params.append(end_date + " 23:59:59")
    if app_id:
        query += " AND app_id = ?"
        count_query += " AND app_id = ?"
        params.append(app_id)
        count_params.append(app_id)
    
    # 添加排序、分页
    query += " ORDER BY publish_at DESC LIMIT ? OFFSET ?"
    params.extend([per_page, offset])
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    # 获取新闻列表
    cursor.execute(query, params)
    news_list = cursor.fetchall()
    
    # 获取总条数
    cursor.execute(count_query, count_params)
    total_count = cursor.fetchone()[0]
    
    # 计算总页数
    total_pages = (total_count + per_page - 1) // per_page
    
    conn.close()

    formatted_news = []
    # 字段顺序：id, title, face_img, order_num, publish_at, media_code, is_del
    for news in news_list:
        face_img = news[2] if news[2] else '/static/images/default.jpg'
        if face_img == '/':
            face_img = '/static/images/default.jpg'
        formatted_news.append([
            news[0],
            news[1],
            face_img,
            news[3],
            news[4],
            news[5],
            news[6]
        ])
    
    # 传递分页相关参数给模板
    return render_template(
        'admin/index.html', 
        news_list=formatted_news, 
        keyword=keyword, 
        start_date=start_date, 
        end_date=end_date, 
        app_id=app_id,
        page=page,
        total_pages=total_pages,
        per_page=per_page,
        total_count=total_count
    )

# 后台登录页面
@app.route('/admin/login', methods=['GET', 'POST'])
def admin_login():
    if request.method == 'POST':
        # 简单的登录验证（实际应用中应该更复杂）
        username = request.form.get('username')
        password = request.form.get('password')
        
        # 这里使用硬编码的用户名和密码，实际应用中应该从数据库中查询
        if username == 'admin' and password == 'admin123':
            session['admin_logged_in'] = True
            return redirect(url_for('admin_index'))
        else:
            return render_template('admin/login.html', error='用户名或密码错误')
    
    return render_template('admin/login.html')

# 后台登出
@app.route('/admin/logout')
def admin_logout():
    session.pop('admin_logged_in', None)
    return redirect(url_for('admin_login'))

# 新闻删除API
@app.route('/admin/news/delete/<int:news_id>', methods=['POST'])
def admin_delete_news(news_id):
    if 'admin_logged_in' not in session:
        return jsonify({'success': False, 'message': '未登录'})
    
    try:
        conn = sqlite3.connect(DATABASE)
        cursor = conn.cursor()
        cursor.execute("UPDATE news SET is_del=1 WHERE id=?", (news_id,))
        conn.commit()
        conn.close()
        return jsonify({'success': True})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)})

# 物理清空news表的所有记录
@app.route('/admin/news/truncate', methods=['POST','GET'])
def admin_truncate_news():
    # 验证管理员登录状态
    if 'admin_logged_in' not in session:
        return jsonify({'success': False, 'message': '未登录'})
    
    try:
        conn = sqlite3.connect(DATABASE)
        cursor = conn.cursor()
        # 使用DELETE FROM语句物理清空表中的所有记录
        cursor.execute("DELETE FROM news")
        # 重置自增ID（如果需要）
        cursor.execute("DELETE FROM sqlite_sequence WHERE name='news'")
        conn.commit()
        conn.close()
        return jsonify({'success': True, 'message': 'news表已成功清空'})
    except Exception as e:
        return jsonify({'success': False, 'message': f'清空表时出错: {str(e)}'})

# 新闻修改页面
@app.route('/admin/news/edit/<int:news_id>', methods=['GET', 'POST'])
def admin_edit_news(news_id):
    if 'admin_logged_in' not in session:
        return redirect(url_for('admin_login'))
    
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    
    if request.method == 'POST':
        # 处理修改请求
        title = request.form.get('title')
        content = request.form.get('content')
        
        # 处理文件上传
        face_img = request.files.get('face_img')

        # print(title)
        # print(face_img.filename)
        #return "ok"
        face_img_path = None
        # 获取新闻详情，包括发布时间字段
        cursor.execute("SELECT id, title, order_num, face_img, publish_at FROM news WHERE id=?", (news_id,))
        news = cursor.fetchone()
        if face_img and face_img.filename:
            # 保存图片到静态文件夹
            file_ext = face_img.filename.rsplit('.', 1)[1].lower()
            if file_ext not in ['jpg', 'jpeg', 'png']:
                conn.close()
                return '不支持的图片格式', 400
            
            order_num = news[2]
            publish_at = news[4]
            publish_date = publish_at.split()[0].replace('-', '')  # 获取年月日格式的发布时间
            filename = f'{order_num}.{file_ext}'
            face_img_path = os.path.join('upload', 'news_face', publish_date, filename)
            face_img.save(os.path.join(app.root_path, face_img_path))
            # face_img_path 转换为 Unix 风格路径
            face_img_path = '/'.join(face_img_path.split('\\'))
            # face_img_path 开始加上网址链接的根路径，确保可以在浏览器中访问，不要域名
            face_img_path = '/'+face_img_path
            cursor.execute("UPDATE news SET face_img=? WHERE id=?", (face_img_path, news_id))
        
        # 保存标题和新闻内容到静态文本文件
        # 构建文件路径：/upload/news_txt/发布时间年月日/新闻编号.txt
        order_num = news[2]
        publish_date = news[4].split()[0].replace('-', '')  # 获取年月日格式的发布时间
        file_path = os.path.join(app.root_path, 'upload', 'news_txt', publish_date, f'{order_num}.txt')
        os.makedirs(os.path.dirname(file_path), exist_ok=True)
        lines = []
        with open(file_path, 'r', encoding='utf-8') as f:
            # 读取前6行
            for i in range(6):
                line = f.readline()
                if line:  # 确保不是空行（文件结束）
                    lines.append(line.strip())  # strip() 去除换行符和空白字符
                else:
                    break  # 如果文件行数不足6行，提前结束
        # 文本文件清空并写入新数据
        with open(file_path, 'w', encoding='utf-8') as f:
            # 第一行是shoubai_c_articleid，第二行是app_id，带字段名
            f.write(f'{lines[0]}\n')
            f.write(f'{lines[1]}\n')
            # 带字段名的新闻信息
            f.write(f'{lines[2]}\n')
            f.write(f'title:{title}\n')
            f.write(f'{lines[4]}\n')
            f.write(f'{lines[5]}\n')
            # 文本内容和HTML内容
            f.write('[[[]]]\n')
            f.write('{{{' + content + '}}}')
        
        # 更新新闻信息
        cursor.execute("UPDATE news SET title=? WHERE id=?", (title, news_id))
        conn.commit()
        conn.close()
        
        return redirect(url_for('admin_index'))
    else:
        # 获取新闻详情，包括发布时间字段
        cursor.execute("SELECT id, title, order_num, face_img, publish_at FROM news WHERE id=?", (news_id,))
        news = cursor.fetchone()
        conn.close()
        
        if not news:
            return '新闻不存在或已删除', 404
        
        # 尝试从文本文件读取content内容
        file_content = ''
        try:
            # 构建文件路径：/upload/news_txt/发布时间年月日/新闻编号.txt
            order_num = news[2]
            publish_date = news[4].split()[0].replace('-', '')  # 获取年月日格式的发布时间
            file_path = os.path.join(app.root_path, 'upload', 'news_txt', publish_date, f'{order_num}.txt')
            
            if os.path.exists(file_path):
                with open(file_path, 'r', encoding='utf-8') as f:
                    txt_content = f.read()
                    # 提取{{{}}}之间的内容
                    import re
                    match = re.search(r'\{\{\{(.+?)\}\}\}', txt_content, re.DOTALL)
                    if match:
                        file_content = match.group(1)
        except Exception as e:
            print(f"读取文件时出错: {str(e)}")
        
        return render_template('admin/edit_news.html', 
                             news={
                                 'id': news[0], 
                                 'title': news[1],
                                 'content': file_content,
                                 'order_num': news[2],
                                 'face_img': news[3]
                             })


# 注册页面
@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        # 处理注册请求（实际应用中应该更复杂，包括表单验证、密码加密等）
        username = request.form.get('username')
        password = request.form.get('password')
        
        # 这里只是简单的示例，实际应用中应该将用户信息存储到数据库
        return redirect(url_for('login'))
    
    return render_template('register.html')

# 访问实例：http://localhost:5000/upload/news_img/20250929/1844594095808148347_1.jpg
# 提供上传图片的路由
@app.route('/upload/news_img/<path:filename>')
def serve_uploaded_image(filename):
    # 指定上传图片的根目录
    upload_dir = os.path.join(app.root_path, 'upload', 'news_img')
    try:
        # 使用send_from_directory函数提供文件下载服务
        return send_from_directory(upload_dir, filename)
    except FileNotFoundError:
        # 如果文件不存在，返回404错误
        return '文件不存在', 404


# 提供上传图片的路由
@app.route('/show/img/<date_str>/<filename>')
def show_news_img(date_str, filename):
    """通过日期和文件名访问图片"""
    image_path = get_image_path(date_str, filename)
    print(image_path)
    # 安全检查
    if not image_path.exists():
        return "图片不存在", 404
    
    # 防止路径遍历攻击
    try:
        image_path.resolve().relative_to(Path(BASE_IMAGE_DIR).resolve())
    except ValueError:
        abort(403)
    
    return send_file(str(image_path))

# 访问实例：http://localhost:5000/upload/news_face/20250929/1844594095808148347_1.jpg
# 提供上传图片的路由
@app.route('/upload/news_face/<path:filename>')
def news_face_show(filename):
    # 指定上传图片的根目录
    upload_dir = os.path.join(app.root_path, 'upload', 'news_face')
    
    try:
        # 使用send_from_directory函数提供文件下载服务
        return send_from_directory(upload_dir, filename)
    except FileNotFoundError:
        # 如果文件不存在，返回404错误
        return '文件不存在', 404
# 登录页面
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        # 处理登录请求（实际应用中应该更复杂，包括密码验证等）
        username = request.form.get('username')
        password = request.form.get('password')
        
        # 这里只是简单的示例，实际应用中应该从数据库验证用户信息
        return redirect(url_for('index'))
    
    return render_template('login.html')

# 辅助函数：获取数据库连接
@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

# 备份news表结构和数据为SQL格式
@app.route('/admin/news/backup', methods=['GET'])
def admin_backup_news():
    """备份news表的结构和数据为SQL格式"""
    # 验证管理员登录状态
    if 'admin_logged_in' not in session:
        return jsonify({'success': False, 'message': '未登录'})
    
    try:
        conn = sqlite3.connect(DATABASE)
        cursor = conn.cursor()
        
        # 获取表结构
        cursor.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name='news'")
        create_table_sql = cursor.fetchone()[0] + ';\n\n'
        
        # 获取所有数据
        cursor.execute("SELECT * FROM news")
        data_rows = cursor.fetchall()
        
        # 获取列名
        cursor.execute("PRAGMA table_info(news)")
        columns_info = cursor.fetchall()
        columns = [col[1] for col in columns_info]
        
        conn.close()
        
        # 构建INSERT语句
        insert_sqls = []
        for row in data_rows:
            # 处理NULL值
            values = []
            for i, value in enumerate(row):
                if value is None:
                    values.append('NULL')
                # 处理字符串值
                elif isinstance(value, str):
                    # 转义单引号
                    escaped_value = value.replace("'", "''")
                    values.append(f"'{escaped_value}'")
                # 处理数值
                else:
                    values.append(str(value))
            
            insert_sql = f"INSERT INTO news ({', '.join(columns)}) VALUES ({', '.join(values)});"
            insert_sqls.append(insert_sql)
        
        # 组合SQL内容
        sql_content = create_table_sql + '\n'.join(insert_sqls)
        
        # 生成带时间戳的文件名
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f'news_backup_{timestamp}.sql'
        
        # 保存到临时文件
        temp_file_path = os.path.join(app.root_path, 'temp_backups', filename)
        
        # 确保临时目录存在
        if not os.path.exists(os.path.join(app.root_path, 'temp_backups')):
            os.makedirs(os.path.join(app.root_path, 'temp_backups'))
        
        # 写入文件
        with open(temp_file_path, 'w', encoding='utf-8') as f:
            f.write(sql_content)
        
        # 发送文件给用户下载
        return send_file(temp_file_path, as_attachment=True, 
                        download_name=filename, 
                        mimetype='text/sql')
        
    except Exception as e:
        return jsonify({'success': False, 'message': f'备份时出错: {str(e)}'})

# 表结构编辑页面
@app.route('/admin/news/structure', methods=['GET'])
def admin_news_structure():
    """
    显示news表结构编辑页面
    """
    # 验证管理员登录状态
    if 'admin_logged_in' not in session:
        return redirect(url_for('admin_login'))
    
    # 处理URL参数中的成功/错误消息
    success_message = request.args.get('success')
    error_message = request.args.get('error')
    
    return render_template('admin/table_structure_edit.html', success_message=success_message, error_message=error_message)

# 表结构数据API
@app.route('/admin/news/structure/data')
def admin_news_structure_data():
    """
    提供news表结构数据的JSON API
    """
    # 验证管理员登录状态
    if 'admin_logged_in' not in session:
        return jsonify({'success': False, 'message': '未登录'})
    
    try:
        conn = sqlite3.connect(DATABASE)
        cursor = conn.cursor()
        
        # 获取news表的结构信息
        cursor.execute("PRAGMA table_info('news')")
        fields = cursor.fetchall()
        
        # 获取索引信息
        cursor.execute("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='news'")
        indexes = cursor.fetchall()
        
        index_dict = {}
        for idx in indexes:
            # 解析索引定义，提取索引字段
            sql = idx[4]
            if 'UNIQUE' in sql.upper():
                index_type = 'UNIQUE'
            else:
                index_type = 'INDEX'
            
            # 提取索引字段（这是一个简化的解析方法）
            field_name = sql.split('ON news(')[1].split(')')[0].strip()
            # 如果有多个字段，只取第一个（实际应用中可能需要更复杂的解析）
            if ',' in field_name:
                field_name = field_name.split(',')[0].strip()
            
            index_dict[field_name] = index_type
        
        # 处理字段信息
        fields_list = []
        for field in fields:
            field_info = {
                'name': field[1],
                'type': field[2],
                'length': None,
                'nullable': not bool(field[3]),
                'default_value': field[4],
                'index': index_dict.get(field[1], None),
                'comment': ''
            }
            
            # 从类型中提取长度信息（如VARCHAR(255)）
            if '(' in field[2] and ')' in field[2]:
                try:
                    type_part = field[2].split('(')[0].strip()
                    length_part = field[2].split('(')[1].split(')')[0].strip()
                    if length_part.isdigit():
                        field_info['type'] = type_part
                        field_info['length'] = int(length_part)
                except:
                    pass
            
            fields_list.append(field_info)
        
        conn.close()
        
        return jsonify({
            'success': True,
            'fields': fields_list
        })
        
    except Exception as e:
        print(f"获取表结构失败: {e}")
        return jsonify({
            'success': False,
            'message': str(e)
        })

# 修改后的保存表结构修改函数
@app.route('/admin/news/structure/edit', methods=['POST'])
def admin_edit_news_structure():
    """
    保存news表结构修改
    """
    # 验证管理员登录状态
    if 'admin_logged_in' not in session:
        return jsonify({'success': False, 'message': '未登录'})
    
    try:
        # 从请求中获取JSON数据
        data = request.get_json()
        if not data:
            return jsonify({'success': False, 'message': '没有获取到字段数据'})
        
        # 提取更改信息
        modified_fields = data.get('modified', [])
        added_fields = data.get('added', [])
        deleted_fields = data.get('deleted', [])
        
        # 连接数据库
        conn = sqlite3.connect(DATABASE)
        cursor = conn.cursor()
        conn.execute('BEGIN TRANSACTION')  # 开始事务
        
        try:
            # 1. 备份当前表结构和数据
            backup_timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            backup_table_name = f"news_backup_{backup_timestamp}"
            
            # 创建备份表
            cursor.execute(f"CREATE TABLE {backup_table_name} AS SELECT * FROM news")
            
            # 2. 获取当前表的字段信息
            cursor.execute("PRAGMA table_info('news')")
            current_fields = cursor.fetchall()
            
            # 3. 构建新表的字段定义
            new_fields_def = []
            primary_key_set = set()
            
            # 处理保留的字段（未被删除的字段）
            for field in current_fields:
                field_name = field[1]
                
                # 跳过被标记为删除的字段
                if field_name in deleted_fields:
                    continue
                
                # 检查字段是否被修改
                modified_field = next((f for f in modified_fields if f['name'] == field_name), None)
                
                if modified_field:
                    # 使用修改后的字段定义
                    field_def = f"{modified_field['name']} {modified_field['type']}"
                    
                    # 添加长度信息（如果有）
                    if modified_field['length']:
                        field_def += f"({modified_field['length']})"
                    
                    # 添加NOT NULL约束
                    if not modified_field['nullable']:
                        field_def += " NOT NULL"
                    
                    # 添加默认值
                    if modified_field['default_value'] is not None:
                        # 处理字符串类型的默认值
                        if modified_field['type'].upper() in ['VARCHAR', 'TEXT', 'DATE', 'DATETIME']:
                            field_def += f" DEFAULT '{modified_field['default_value']}'"
                        else:
                            field_def += f" DEFAULT {modified_field['default_value']}"
                    
                    # 检查是否为主键（简化处理，实际应用中可能需要更复杂的逻辑）
                    if field_name == 'id':
                        field_def += " PRIMARY KEY AUTOINCREMENT"
                else:
                    # 使用原始字段定义
                    field_def = f"{field_name} {field[2]}"
                    if field[3]:  # NOT NULL
                        field_def += " NOT NULL"
                    if field[5]:  # PRIMARY KEY
                        field_def += " PRIMARY KEY"
                        primary_key_set.add(field_name)
                        # 检查是否自增（简化处理）
                        if field_name == 'id':
                            field_def += " AUTOINCREMENT"
                
                # 将处理后的字段定义添加到新表字段列表中
                new_fields_def.append(field_def)
            
            # 添加新字段
            for field in added_fields:
                field_def = f"{field['name']} {field['type']}"
                
                # 添加长度信息（如果有）
                if field['length']:
                    field_def += f"({field['length']})"
                
                # 添加NOT NULL约束
                if not field['nullable']:
                    field_def += " NOT NULL"
                
                # 添加默认值
                if field['default_value'] is not None:
                    # 处理字符串类型的默认值
                    if field['type'].upper() in ['VARCHAR', 'TEXT', 'DATE', 'DATETIME']:
                        field_def += f" DEFAULT '{field['default_value']}'"
                    else:
                        field_def += f" DEFAULT {field['default_value']}"
                
                new_fields_def.append(field_def)
            
            # 4. 创建新表
            create_table_sql = "CREATE TABLE IF NOT EXISTS news_new ("
            create_table_sql += ", ".join(new_fields_def)
            create_table_sql += ")"
            
            cursor.execute(create_table_sql)
            
            # 5. 将数据从备份表导入到新表
            # 获取新旧表的共有字段
            cursor.execute("PRAGMA table_info('news_new')")
            new_table_fields = [field[1] for field in cursor.fetchall()]
            
            cursor.execute(f"PRAGMA table_info('{backup_table_name}')")
            backup_table_fields = [field[1] for field in cursor.fetchall()]
            
            # 找出共有字段（注意：考虑字段名称可能被修改的情况）
            common_fields = []
            field_mapping = {}
            
            # 检查原始字段名
            for field in backup_table_fields:
                if field in new_table_fields:
                    common_fields.append(field)
                    field_mapping[field] = field
            
            # 检查是否有修改过名称的字段
            for modified_field in modified_fields:
                original_name = next((f[1] for f in current_fields if f[1] == modified_field['name']), None)
                if original_name and original_name in backup_table_fields and modified_field['name'] in new_table_fields:
                    if original_name not in field_mapping:
                        common_fields.append(original_name)
                        field_mapping[original_name] = modified_field['name']
            
            if common_fields:
                # 构建INSERT语句
                source_fields = ', '.join(common_fields)
                target_fields = ', '.join([field_mapping[field] for field in common_fields])
                placeholders = ', '.join([f':{field}' for field in common_fields])
                
                # 从备份表获取数据
                cursor.execute(f"SELECT {source_fields} FROM {backup_table_name}")
                rows = cursor.fetchall()
                
                # 批量插入数据到新表
                if rows:
                    insert_sql = f"INSERT INTO news_new ({target_fields}) VALUES ({placeholders})"
                    
                    # 转换行数据为字典列表
                    rows_dict = []
                    for row in rows:
                        row_dict = {}
                        for i, field in enumerate(common_fields):
                            row_dict[field] = row[i]
                        rows_dict.append(row_dict)
                    
                    cursor.executemany(insert_sql, rows_dict)
            
            # 6. 创建索引
            for field in modified_fields + added_fields:
                if field.get('index'):
                    index_name = f"idx_news_{field['name']}"
                    if field['index'] == 'UNIQUE':
                        cursor.execute(f"CREATE UNIQUE INDEX IF NOT EXISTS {index_name} ON news_new({field['name']})")
                    else:
                        cursor.execute(f"CREATE INDEX IF NOT EXISTS {index_name} ON news_new({field['name']})")
            
            # 7. 重命名表（需要先删除原表）
            cursor.execute("DROP TABLE IF EXISTS news")
            cursor.execute("ALTER TABLE news_new RENAME TO news")
            
            # 8. 提交事务
            conn.commit()
            
            # 9. 清理备份表（可选，为了安全可以保留一段时间）
            # cursor.execute(f"DROP TABLE IF EXISTS {backup_table_name}")
            # conn.commit()
            
            conn.close()
            
            return jsonify({
                'success': True,
                'message': '表结构修改成功'
            })
            
        except Exception as e:
            # 发生错误时回滚事务
            conn.rollback()
            print(f"修改表结构过程中出错: {e}")
            raise e
            
    except Exception as e:
        print(f"修改表结构失败: {e}")
        return jsonify({
            'success': False,
            'message': str(e)
        })

# 在文件末尾添加应用启动代码
if __name__ == '__main__':
    # 启动Flask应用，开启debug模式便于开发调试
    app.run(debug=True, host='0.0.0.0', port=5000)